﻿
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OCAP_FACAEmail_Api.AppCode;
using OCAP_FACAEmail_Api.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace OCAP_FACAEmail_Api.Controllers
{
    public class FACAEmail2Controller : Controller
    {
        // GET: FACAEmail2
        public ActionResult Index()
        {
            return View();
        }
        //查
        public JsonResult GetMailInfo2(int pageNum, int pageSize, string deviceCode)
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper(2);
                List<DeviceMailInfo2> list = new List<DeviceMailInfo2>();

                string oldCmd = $"SELECT * FROM [dbo].[OCAP_EMail] Where 1=1";
                if (!(deviceCode is null))
                {
                    oldCmd += $"And deviceCode = '{deviceCode}'";
                }
                string cmd = string.Empty;
                if (pageNum != 0 && pageSize != 0)
                {
                    cmd = oldCmd + $" order by deviceCode offset {(pageNum - 1) * pageSize} rows fetch next {pageSize} rows only";
                }
                var th = sqlHelper.GetData(oldCmd);
                int total = sqlHelper.GetData(cmd).Rows.Count;

                for (int i = 0; i < th.Rows.Count; i++)
                {
                    DeviceMailInfo2 mailInfo = new DeviceMailInfo2();
                    mailInfo.index = ((pageNum - 1) * pageSize + i + 1).ToString();
                    mailInfo.deviceCode = th.Rows[i]["deviceCode"].ToString();
                    mailInfo.ID = th.Rows[i]["ID"].ToString();
                    mailInfo.FACA1 = th.Rows[i]["FACA1"].ToString();
                    mailInfo.FACA2 = th.Rows[i]["FACA2"].ToString();
                    mailInfo.FACA3 = th.Rows[i]["FACA3"].ToString();
                    mailInfo.QC1 = th.Rows[i]["QC1"].ToString();
                    mailInfo.QC2 = th.Rows[i]["QC2"].ToString();
                    mailInfo.QC3 = th.Rows[i]["QC3"].ToString();
                    mailInfo.QA1 = th.Rows[i]["QA1"].ToString();
                    mailInfo.QA2 = th.Rows[i]["QA2"].ToString();
                    list.Add(mailInfo);
                }
                return Json(new
                {
                    msg = "操作成功",
                    code = 200,
                    info = list,
                    total = total
                }, JsonRequestBehavior.AllowGet);
            }

            catch (Exception ex)
            {
                return Json(new
                {
                    msg = ex.Message,
                    code = 500,
                }, JsonRequestBehavior.AllowGet);
            }
        }
        //添加
        public JsonResult AddDeviceMail2(DeviceMailInfo2 info)
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper(2);
                string cmd = $"Select deviceCode From OCAP_Email with(nolock) Where deviceCode='{info.deviceCode}'";
                var th = sqlHelper.GetData(cmd);
                if (th.Rows.Count > 0)
                {
                    return Json(new
                    {
                        msg = "该设备已存在，请勿重复添加",
                        code = 500,
                    }, JsonRequestBehavior.AllowGet);
                }
                else
                {
                    string cmd1 = $"insert into OCAP_Email (deviceCode,FACA1,FACA2,FACA3,QC1,QC2,QC3,QA1,QA2)" +
                     $"values ('{info.deviceCode}','{info.FACA1}','{info.FACA2}','{info.FACA3}','{info.QC1}','{info.QC2}'," +
                     $"'{info.QC3}','{info.QA1}','{info.QA2}')";
                    sqlHelper.ExecuteNonQuery(cmd1);
                   
                    return Json(new
                    {
                        msg = "添加成功",
                        code = 200,
                    }, JsonRequestBehavior.AllowGet);
                }
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    msg = ex.Message,
                    code = 500,
                }, JsonRequestBehavior.AllowGet);
            }
        }
        //修改
        public JsonResult EditDeviceMail2(DeviceMailInfo2 info,string oldDeviceCode)
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper(2);
                string cmd = string.Empty;
                cmd = $"Select deviceCode From OCAP_Email Where ID != '{info.ID}' And deviceCode='{info.deviceCode}'";
                var th = sqlHelper.GetData(cmd);
                if (th.Rows.Count > 0)
                {
                    return Json(new
                    {
                        msg = "该设备编号已存在，请勿重复添加",
                        code = 500,
                    }, JsonRequestBehavior.AllowGet);
                }
                else
                {
                    string updateCmd = $"UPDATE OCAP_Email SET deviceCode ='{info.deviceCode}', FACA1='{info.FACA1}', FACA2='{info.FACA2}', FACA3='{info.FACA3}',QC1='{info.QC1}',QC2='{info.QC2}'" +
                    $",QC3='{info.QC3}',QA1='{info.QA1}',QA2='{info.QA2}'" +
                    $" where deviceCode ={oldDeviceCode}";
                    sqlHelper.ExecuteNonQuery(updateCmd);
                    return Json(new
                    {
                        msg = "修改成功",
                        code = 200,
                    }, JsonRequestBehavior.AllowGet);
                }
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    msg = ex.Message,
                    code = 500,
                }, JsonRequestBehavior.AllowGet);
            }
        }
        //删除
        public JsonResult DelDeviceMail2(List<object> deviceCodes)
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper(2);
                string cmd = string.Empty;
                foreach (var item in deviceCodes)
                {

                    cmd = $"DELETE FROM OCAP_Email Where deviceCode='{item}'";
                    sqlHelper.ExecuteNonQuery(cmd);
                }
                return Json(new
                {
                    msg = "删除成功",
                    code = 200,
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    msg = ex.Message,
                    code = 500,
                }, JsonRequestBehavior.AllowGet);
            }
        }
        //是否为空行
        public bool RowIsEmpty(IRow row)
        {
            if (row == null)
            {
                return true;
            }
            else
            {
                for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
                {
                    if (row.GetCell(i) != null && !string.IsNullOrEmpty(row.GetCell(i).ToString()))
                    {
                        return false;
                    }
                }
                return true;

            }
        }
        //文件导入
        public JsonResult uploadExcel()
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper(2);
                var files = HttpContext.Request.Files;
                var file = files[0];
                XSSFWorkbook wbk = new XSSFWorkbook(file.InputStream);
                ISheet sheet = wbk.GetSheetAt(0);
                DataTable dt = new DataTable();//创建空表
                // 获取表头
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                for (int i = 0; i < cellCount; i++)
                {
                    ICell cell = headerRow.GetCell(i);
                    string columnName = cell.StringCellValue;//获取单元格的值
                    dt.Columns.Add(columnName);
                }
                // 获取数据总行数
                int rowCount = sheet.LastRowNum;

                // 存放单元格存在公式数据.
                List<Object> EList = new List<Object>();

                for (int i = 1; i <= rowCount; i++)//跳过表头
                {
                    IRow row = sheet.GetRow(i);

                    //if (row == null) continue;//跳过空行不处理
                    if (RowIsEmpty(row)) continue;//跳过空行不处理
                    DataRow dr = dt.NewRow();//新行
                    Boolean HadError = false;  // 该行数据是否存在错误

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    if (string.IsNullOrEmpty(dr["设备编号"].ToString()))
                    {
                        EList.Add($"第{i + 1}行的设备编号为空. 该行没有录入");
                        HadError = true;
                    }
                    // 没有错误加入后续处理
                    if (!HadError)
                    {
                        dt.Rows.Add(dr);
                    }
                }
                List<DeviceMailInfo2> list = new List<DeviceMailInfo2>();
                Boolean flag = true;
                // 横向遍历
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DeviceMailInfo2 Info = new DeviceMailInfo2();
                    Info.deviceCode = dt.Rows[i]["设备编号"].ToString() == null ? "null" : $" '{dt.Rows[i]["设备编号"]}'";
                    Info.FACA1 = dt.Rows[i]["FACA工程师"].ToString() == "" ? "null" : $" '{dt.Rows[i]["FACA工程师"]}'";
                    Info.FACA2 = dt.Rows[i]["FACA课长"].ToString() == null ? "null" : $" '{dt.Rows[i]["FACA课长"]}'";
                    Info.FACA3 = dt.Rows[i]["FACA部长"].ToString() == null ? "null" : $" '{dt.Rows[i]["FACA部长"]}'";
                    Info.QC1 = dt.Rows[i]["QC工程师"].ToString() == null ? "null" : $" '{dt.Rows[i]["QC工程师"]}'";
                    Info.QC2 = dt.Rows[i]["QC课长"].ToString() == null ? "null" : $" '{dt.Rows[i]["QC课长"]}'";
                    Info.QC3 = dt.Rows[i]["QC部长"].ToString() == null ? "null" : $" '{dt.Rows[i]["QC部长"]}'";
                    Info.QA1 = dt.Rows[i]["QA部长"].ToString() == null ? "null" : $" '{dt.Rows[i]["QA部长"]}'";
                    Info.QA2 = dt.Rows[i]["QA本部长"].ToString() == null ? "null" : $" '{dt.Rows[i]["QA本部长"]}'";
                    list.Add(Info);
                }
                //插入数据库
                InsertData(list);
                return Json(new
                {
                    msg = "操作成功",
                    code = 200,
                    flag,
                    EList,
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    msg = ex.Message,
                    code = 500,
                }, JsonRequestBehavior.AllowGet);
            }

        }
        //插入数据库
        public static void InsertData(List<DeviceMailInfo2> list)
        {
            SqlHelper sqlHelper = new SqlHelper(2);
            for (int i = 0; i < list.Count; i++)
            {
                string isGDZC = $"SELECT deviceCode FROM OCAP_Email with(nolock) WHERE deviceCode={list[i].deviceCode}";
                var isTh = sqlHelper.GetData(isGDZC);
                //已存在该设备编号,更新
                if (isTh.Rows.Count > 0)
                {
                    string updateCmd = $"UPDATE OCAP_Email SET FACA1={list[i].FACA1},FACA2={list[i].FACA2},FACA3={list[i].FACA3},QC1={list[i].QC1},QC2={list[i].QC2}" +
                        $",QC3={list[i].QC3},QA1={list[i].QA1},QA2={list[i].QA2}" +
                        $" where deviceCode ={list[i].deviceCode}";
                    sqlHelper.ExecuteNonQuery(updateCmd);
                }
                //else插入
                else
                {
                    string cmd = $"insert into OCAP_Email (deviceCode,FACA1,FACA2,FACA3,QC1,QC2,QC3,QA1,QA2)" +
                    $"values({list[i].deviceCode},{list[i].FACA1},{list[i].FACA2},{list[i].FACA3},{list[i].QC1},{list[i].QC2}," +
                    $"{list[i].QC3},{list[i].QA1},{list[i].QA2})";
                    sqlHelper.ExecuteNonQuery(cmd);
                }

            }
        }
        #region 获取OCAP_DRI_EMail表数据
        public JObject OptsList()
        {
            try
            {
                Stream postData = Request.InputStream;
                var postContent = string.Empty;
                MemoryStream postStream = new MemoryStream();
                postData.CopyTo(postStream);
                postStream.Position = 0;
                postData.Position = 0;
                using (StreamReader reader = new StreamReader(postStream))
                {
                    postContent = reader.ReadToEnd();
                }
                JObject jobject = ((JObject)JsonConvert.DeserializeObject(postContent)) ?? throw new Exception("no json");

                HashSet<string> UsefulParam = new HashSet<string>(); //可识别的参数

                string Method = CommUtil.getJobVal(jobject, "Method", UsefulParam, true);
                string Value = CommUtil.getJobVal(jobject, "Value", UsefulParam, true);
                Method = Method.ToLower();

                SqlHelper sqlHelper = new SqlHelper(2);
                string sqlCmd = string.Empty;
                DataTable sqlTb = null;

                switch (Method)
                {
                    case "select":
                        {
                            string[] ignoreRowName = new string[] { "pageNum", "pageSize" };
                            JToken job = (JToken)JsonConvert.DeserializeObject(Value) ?? new JObject();
                            string addParam = string.Empty;
                            foreach (var bigkey in job)
                            {
                                var property = bigkey as JProperty;
                                string key = property.Name.ToString();
                                string value = property.Value.ToString();

                                if (string.IsNullOrWhiteSpace(value))
                                {
                                    continue; //为空跳过
                                }

                                string find = ignoreRowName.FirstOrDefault(x => x.Equals(key));
                                if (find != null)
                                {
                                    continue; //跳过特定列
                                }


                                CommUtil.getJobVal(null, key, UsefulParam); //添加识别参数list
                                addParam += $" and l.\"{key}\"= '{value}' ";

                            }
                            sqlCmd = $"SELECT Prefix_dept FROM [dbo].[OCAP_DRI_EMail] with(nolock) GROUP BY Prefix_dept";
                            sqlTb = sqlHelper.GetData(sqlCmd);
                            JArray ja = new JArray();
                            var list = sqlTb.AsEnumerable().Select(x =>
                            {
                                var Obj = new JObject();
                                Obj["Prefix_dept"] = x["Prefix_dept"].ToString().Trim();

                                string sqlCmd2 = $"SELECT * FROM [dbo].[OCAP_DRI_EMail] with(nolock) Where Prefix_dept='{x["Prefix_dept"]}'";
                                var th2 = sqlHelper.GetData(sqlCmd2);
                                List<Object> DRI_List = new List<Object>();
                                for (int p = 0; p < th2.Rows.Count; p++)
                                {
                                    DRI_List.Add(th2.Rows[p]["DRI"]);
                                }
                                Obj["DRI_List"] = JToken.FromObject(DRI_List);
                                return Obj;
                            }).ToList();
                            ja = new JArray(list);
                            return new JObject() { { "Result", 1 }, { "Msg", "OK" }, { "Value", JToken.FromObject(ja) }, { "UsefulParam", JToken.FromObject(UsefulParam) } };

                        }
                        break;
                    default:
                        {
                            throw new Exception("未知方法");
                        }
                        break;
                }
                return new JObject() { { "Result", 1 }, { "Msg", "OK" }, { "Value", "处理成功" }, { "UsefulParam", JToken.FromObject(UsefulParam) } };

            }
            catch (Exception ex)
            {
                return new JObject() { { "Result", 2 }, { "Msg", "ERROR" }, { "Value", ex.Message } };
            }
        }
        #endregion
    }
}